之前寫的  以Postgresql為主,聊聊資料庫
有一篇 四探 Postgresql Catalog 

https://ithelp.ithome.com.tw/articles/10218574

是關於建立 table 時,pg_catalog 會發生什麼變化.

今天來跟大家聊聊刪除欄位時,pg_catalog 裡關於欄位屬性的
pg_attribute, 以及 disk space.

create table foo200210 (
  n int not null
);

-- insert 一千萬筆
insert into foo200210 
select generate_series(1, 10000000);

INSERT 0 10000000
Time: 15468.770 ms (00:15.469)

commit;

-- query table size
select pg_size_pretty(pg_relation_size('foo200210'));

+----------------+
| pg_size_pretty |
+----------------+
| 346 MB         |
+----------------+

-- 接著增加一個 text 欄位,存放隨機資料.
alter table foo200210
  add column t text default md5(random()::text);

ALTER TABLE
Time: 32292.338 ms (00:32.292)

新增一千萬筆隨機文字資料,花費約32秒.

commit;

-- query table size
select pg_size_pretty(pg_relation_size('foo200210'));

+----------------+
| pg_size_pretty |
+----------------+
| 651 MB         |
+----------------+

可以觀察到 table size 幾乎倍增.
接著來看看 pg_catalog 裡面關於欄位的一些屬性

select attnum
     , attname
     , attisdropped
  from pg_attribute a
  join pg_class c
    on c.oid = a.attrelid
   and c.relname = 'foo200210'
   and c.relkind = 'r'
   and a.attnum > 0;

+--------+---------+--------------+
| attnum | attname | attisdropped |
+--------+---------+--------------+
|      1 | n       | f            |
|      2 | t       | f            |
+--------+---------+--------------+
(2 rows)

可以觀察到兩個欄位的屬性中有 "是否已經刪除" (attisdropped), 目前都是 false

接著我們來把 text 型態的欄位刪除.

alter table foo200210
 drop column t;

ALTER TABLE
Time: 15.217 ms

觀察到只用了 15.217 ms. 

commit;

接著來看欄位情況以及size

select attnum
     , attname
     , attisdropped
  from pg_attribute a
  join pg_class c
    on c.oid = a.attrelid
   and c.relname = 'foo200210'
   and c.relkind = 'r'
   and a.attnum > 0;

+--------+------------------------------+--------------+
| attnum |           attname            | attisdropped |
+--------+------------------------------+--------------+
|      1 | n                            | f            |
|      2 | ........pg.dropped.2........ | t            |
+--------+------------------------------+--------------+

select pg_size_pretty(pg_relation_size('foo200210'));

+----------------+
| pg_size_pretty |
+----------------+
| 651 MB         |
+----------------+

可以看到 size 不變,欄位屬性被標示為已刪除(true).欄位名稱改了.
試試看能不能查詢?

select n
     , "........pg.dropped.2........"
  from foo200210
 limit 1;
ERROR:  42703: column "........pg.dropped.2........" does not exist

看到size 不變,相信對 Postgresql 比較熟悉的讀者,應該會想到 vacuum.

我們先來增加另一個欄位,再觀察size與欄位屬性.

alter table foo200210
  add column t2 text default md5(random()::text);

ALTER TABLE
Time: 34409.649 ms (00:34.410)

commit;

select attnum
     , attname
     , attisdropped
  from pg_attribute a
  join pg_class c
    on c.oid = a.attrelid
   and c.relname = 'foo200210'
   and c.relkind = 'r'
   and a.attnum > 0;

+--------+------------------------------+--------------+
| attnum |           attname            | attisdropped |
+--------+------------------------------+--------------+
|      1 | n                            | f            |
|      2 | ........pg.dropped.2........ | t            |
|      3 | t2                           | f            |
+--------+------------------------------+--------------+
(3 rows)

select pg_size_pretty(pg_relation_size('foo200210'));
+----------------+
| pg_size_pretty |
+----------------+
| 651 MB         |
+----------------+

可以觀察到 size 相同(只是size值相同),
新增了欄位 t2.
接著做 vacuum .

commit;
vacuum full foo200210;

VACUUM
Time: 17088.505 ms (00:17.089)

真的清理了.花了約17秒.

size 還是相同.

select pg_size_pretty(pg_relation_size('foo200210'));
+----------------+
| pg_size_pretty |
+----------------+
| 651 MB         |
+----------------+

select attnum
     , attname
     , attisdropped
  from pg_attribute a
  join pg_class c
    on c.oid = a.attrelid
   and c.relname = 'foo200210'
   and c.relkind = 'r'
   and a.attnum > 0;
   
+--------+------------------------------+--------------+
| attnum |           attname            | attisdropped |
+--------+------------------------------+--------------+
|      1 | n                            | f            |
|      2 | ........pg.dropped.2........ | t            |
|      3 | t2                           | f            |
+--------+------------------------------+--------------+
(3 rows)

佔的位置還是在 pg_attribute 裡面.後續新增欄位,就是遞增,不往上遞補位置.

相信透過上面操作及觀察,對 Postgresql 的 Table 空間,欄位屬性以及 vacuum 機制,
會有更清楚的理解.